﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Transactions;
using Components;
using Model;

namespace MsDAL
{
    public class DBHelper
    {
        private static string _connStr = "";//把连接字符串申明为一个私有的静态的成员变量
        private static string _debug = ConfigurationManager.AppSettings["b"].ToUpper();

        public static string ConnStr
        {
            get
            {
                if (_connStr == "")
                    _connStr = ConfigurationManager.AppSettings["a"];//SecurityEncryption.Decrypt();
                return _connStr;
            }
        }

        /// <summary>
        /// 此方法用于获得数据库连接
        /// </summary>
        /// <returns>返回连接</returns>
        public static SqlConnection CreateConnection()
        {
            SqlConnection con = new SqlConnection(ConnStr);
            return con;
        }


        /// <summary>
        /// 此方法用于非查询操作
        /// </summary>
        /// <param name="sql">要做非查询的Sql语句</param>
        /// <param name="pars">Sql语句的参数，没有的话可以不传或传null</param>
        /// <returns></returns>
        public static bool DoSql(String sql, params SqlParameter[] pars)
        {
            int count = 0;
            using (SqlConnection con = CreateConnection())
            {
                try
                {
                    using (SqlCommand cmd = GetCommandBySql(sql, pars))
                    {
                        cmd.Connection = con;
                        con.Open();
                        count = cmd.ExecuteNonQuery();
                        con.Close();
                    }
                }
                catch (SqlException sex)
                {
                    LogHelper.Entity.Out(sex);
                    throw new Exception(("Y" == _debug) ? "SQL:" + sql + "\n" + sex.Message : "数据库正忙，操作失败！");
                }
            }
            return count > 0;
        }


        public static string BatchDoSql(List<SqlApp> lst)
        {
            string sql = "";
            string msg = "";
            object[] objs = null;
            SqlTransaction str = null;
            using (SqlConnection con = CreateConnection())
            {
                if (con.State != ConnectionState.Open)
                    con.Open();

                //外部是否有事务保护
                var hasExternalTran = Transaction.Current != null;

                //启用事务保护
                if (hasExternalTran == false)
                    str = con.BeginTransaction();

                SqlCommand cmd = null;

                try
                {
                    if (null == lst) return "";

                    int count = -1;
                    foreach (SqlApp sp in lst)
                    {
                        if (null == sp) continue;

                        sql = sp.Sql;
                        objs = (null == sp.SqlParam) ? null : (object[])sp.SqlParam.ToArray(typeof(object));
                        msg = sp.ErrorMsg;

                        using (cmd = GetCommandBySql(sql, objs))
                        {
                            if (str != null)
                                cmd.Transaction = str;

                            cmd.Connection = con;
                            if (con.State != ConnectionState.Open) con.Open();
                            count = cmd.ExecuteNonQuery();

                            if (count == 0 && sp.IsAllowNull == false) //影响行数为0且不允许影响行为0，报错
                            {
                                var errorMsg = string.IsNullOrEmpty(sp.ErrorMsg) ? "数据保存失败！" : sp.ErrorMsg;

                                if (str != null)
                                    str.Rollback();
                                else
                                    throw new StnException(errorMsg);

                                return errorMsg;
                            }
                        }
                    }

                    if (str != null)
                        str.Commit();
                    return "";
                }
                catch (Exception sex)
                {
                    if (str != null)
                        str.Rollback();
                    LogHelper.Entity.Out(cmd);
                    LogHelper.Entity.Out(sex.Message);
                    return string.IsNullOrEmpty(msg) ? sex.Message : msg; //如果SQL对象的错误消息为空，默认返回异常信息
                }
            }
        }

        /// <summary>
        /// 此方法用于返回首行首列
        /// </summary>
        /// <param name="sql">要做非查询的Sql语句</param>
        /// <param name="pars">Sql语句的参数，没有的话可以不传或传null</param>
        /// <returns></returns>
        public static Object ExecuteScalar(String sql, params SqlParameter[] pars)
        {
            Object obj = null;
            using (SqlConnection con = CreateConnection())
            {
                try
                {
                    using (SqlCommand cmd = GetCommandBySql(sql, pars))
                    {
                        cmd.Connection = con;
                        con.Open();
                        obj = cmd.ExecuteScalar();
                        con.Close();
                    }
                }
                catch (SqlException sex)
                {
                    LogHelper.Entity.Out(sex);
                    throw new Exception(("Y" == _debug) ? "SQL:" + sql + "\n" + sex.Message : "数据库正忙，操作失败！");
                }
            }
            return obj;
        }

        /// <summary>
        /// 此方法用于获得表的数据
        /// </summary>
        /// <param name="sql">要做查询的Sql语句</param>
        /// <param name="paras">Sql语句的参数，没有的话可以不传或传null</param>
        /// <returns>返回DataTable</returns>
        public static DataTable GetData(String sql, params SqlParameter[] paras)
        {
            DataSet ds = DBHelper.GetDataSet(sql, paras);
            if (ds == null || 0 == ds.Tables.Count) return null;
            return ds.Tables[0];
        }

        public static DataSet GetDataSet(String sql, params SqlParameter[] paras)
        {
            DataSet ds = null;
            SqlCommand cmd = null;
            using (SqlConnection con = CreateConnection())
            {
                try
                {
                    using (cmd = GetCommandBySql(sql, paras))
                    {
                        cmd.Connection = con;
                        SqlDataAdapter sda = new SqlDataAdapter(cmd);
                        ds = new DataSet();
                        sda.Fill(ds);
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Entity.Out(ex);
                    LogHelper.Entity.Out(cmd);
                    throw new Exception(("Y" == _debug) ? "SQL:" + sql + "\n" + ex.Message : "数据库正忙，操作失败！");
                }
            }
            return ds;
        }

        /// <summary>
        /// 此方法用于获得一个DataReader对象
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="paras">参数集合</param>
        /// <returns>DataReader对象</returns>
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras)
        {
            SqlDataReader dr = null;
            try
            {
                using (SqlConnection con = CreateConnection())
                {
                    using (SqlCommand cmd = GetCommandBySql(sql, paras))
                    {
                        cmd.Connection = con;
                        con.Open();

                        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                }

            }
            catch (Exception sex)
            {
                LogHelper.Entity.Out(sex);
                throw new Exception(("Y" == _debug) ? "SQL:" + sql + "\n" + sex.Message : "数据库正忙，操作失败！");
            }

            return dr;
        }

        /// <summary>
        /// 得到SqlCommand对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        private static SqlCommand GetCommandBySql(string sql, params object[] paras)
        {
            SqlParameter para = null;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = sql;
            if (paras != null)
            {
                foreach (object o in paras)
                {
                    if (o is SqlParameter)
                        para = (SqlParameter)o;
                    else
                        throw new Exception("参数格式错误！");

                    //判断值是否为空，如果为空转换为DBNull.Value
                    if (para.Value == null)
                        para.Value = DBNull.Value;
                    cmd.Parameters.Add(para);
                }
            }
            return cmd;
        }


        public static DataTable ExecuteProc(string sql, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                try
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.CommandText = sql;
                    if (paras != null)
                    {
                        foreach (SqlParameter para in paras)
                        {
                            //判断值是否为空，如果为空转换为DBNull.Value
                            if (para.Value == null)
                                para.Value = DBNull.Value;

                            cmd.Parameters.Add(para);
                            //para.Direction = para.Direction;
                        }
                    }

                    DataSet ds = new DataSet();
                    SqlDataAdapter sdr = new SqlDataAdapter(cmd);

                    sdr.Fill(ds);

                    return ds.Tables[0];
                }
                catch (Exception sex)
                {
                    LogHelper.Entity.Out(sex);
                    throw new Exception(("Y" == _debug) ? "SQL:" + sql + "\n" + sex.Message : "数据库正忙，操作失败！");
                }
            }
        }

        /// <summary>
        /// 根据哈希表，把参数和装入List<SqlParameter>中
        /// </summary>
        /// <param name="hs">装满值和参数的哈希表</param>
        /// <returns></returns>
        public static List<SqlParameter> GetListByHashtable(Hashtable hs)
        {
            List<SqlParameter> list = new List<SqlParameter>();
            if (hs != null)
            {
                foreach (DictionaryEntry entry in hs)
                {
                    list.Add(new SqlParameter(entry.Key.ToString(), entry.Value));
                }
            }

            return list;
        }

        /// <summary>
        /// 根据条件得到DataTable
        /// </summary>
        /// <param name="Condition">Sql中的条件</param>
        /// <param name="hs">参数和值</param>
        /// <returns></returns>
        public static DataTable GetTableByCondition(String sql, Hashtable hs)
        {
            List<SqlParameter> list = null;

            if (hs != null)
                list = GetListByHashtable(hs);

            DataTable dt = null;
            if (list != null)
                dt = DBHelper.GetData(sql, list.ToArray());
            else
                dt = DBHelper.GetData(sql);
            return dt;
        }

        /// <summary>
        /// 根据条件得到Object
        /// </summary>
        /// <param name="Condition">Sql中的条件</param>
        /// <param name="hs">参数和值</param>
        /// <returns></returns>
        public static Object DoSqlByCondition(String sql, Hashtable hs)
        {
            List<SqlParameter> list = null;

            if (hs != null)
                list = GetListByHashtable(hs);

            return (list != null) ? DBHelper.ExecuteScalar(sql, list.ToArray())
                : DBHelper.ExecuteScalar(sql);
        }

        /// <summary>
        /// 分页方法
        /// </summary>
        /// <param name="page"></param>
        /// <param name="sqlKey">由于可能有表连接，所以可以传一个XML中的key文件过来</param>
        /// <returns></returns>
        public static DataTable GetSelectLimit(Pager page)
        {
            string sql = "GetRecordFromPage";

            SqlParameter[] parameters = {
					new SqlParameter("@tblName",SqlDbType.VarChar),  //这个地方的字段名是数据库中的字段名
					new SqlParameter("@fldName", SqlDbType.VarChar),
					new SqlParameter("@showfld", SqlDbType.VarChar),
					new SqlParameter("@PageSize", SqlDbType.Int),
					new SqlParameter("@PageIndex", SqlDbType.Int),
					new SqlParameter("@mode", SqlDbType.NVarChar),
					new SqlParameter("@orderFld", SqlDbType.VarChar),
					new SqlParameter("@strWhere", SqlDbType.VarChar),
                    new SqlParameter("@isReadDbSource", SqlDbType.Bit),
                    new SqlParameter("@pagename", SqlDbType.VarChar),
                    new SqlParameter("@comcode", SqlDbType.VarChar),
                    new SqlParameter("@usercode", SqlDbType.VarChar)
					};
            parameters[0].Value = page.TableName;
            parameters[1].Value = page.PrimaryKey;
            parameters[2].Value = page.ShowFields;
            parameters[3].Value = page.PageSize;
            parameters[4].Value = page.PageIndex;
            parameters[5].Value = page.For;
            //如果系统有排序，那么覆盖掉用户的排序
            parameters[6].Value = (string.IsNullOrEmpty(page.AutoOrder)) ? page.OrderFid : page.AutoOrder;
            parameters[7].Value = page.StrWhere.Trim();
            parameters[8].Value = page.IsReadDbSource;
            parameters[9].Value = page.PageName;
            parameters[10].Value = page.Comcode;
            parameters[11].Value = page.Usercode;

            return ExecuteProc(sql, parameters);
        }
    }
}
